package com.tjetc.dao.impl;

import com.tjetc.dao.DepartmentDao;
import com.tjetc.domain.Department;
import com.tjetc.domain.StaffsCount;
import com.tjetc.utils.Db;
import com.tjetc.utils.PageInfo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class DepartmentDaoImpl implements DepartmentDao {
    @Override
    public int getCount(String name) throws SQLException {
        return ((Long)Db.runner0().query("select count(id) from department where name like ?",
                new ScalarHandler<>(),"%"+name+"%")).intValue();
    }

    @Override
    public List<Department> list(String name, PageInfo<Department> pageInfo) throws SQLException {
        return Db.runner0().query("select * from department where name like ? limit ?,? ",
                new BeanListHandler<>(Department.class),"%"+name+"%",
                pageInfo.getStartRecord(),pageInfo.getPageSize());
    }

    @Override
    public int add(Department department) throws SQLException {
        return Db.runner0().update("insert into department(name, remarks, did, staffs, beds, parent_id) values (?,?,?,?,?,?)",
                department.getName(),department.getRemarks(),department.getDid(),department.getStaffs(),
                department.getBeds(),department.getParent_id());
    }

    @Override
    public int update(Department department) throws SQLException {
        return Db.runner0().update("update department set name=?,remarks=?,did=?,staffs=?,beds=?,parent_id=? where id = ?",
                department.getName(),department.getRemarks(),department.getDid(),department.getStaffs(),
                department.getBeds(),department.getParent_id(),department.getId());
    }

    @Override
    public int del(int id) throws SQLException {
        return Db.runner0().update("delete from department where id = ?",id);
    }

    @Override
    public int delByPid(int pid) throws SQLException {
        return Db.runner().update(Db.getConnection(),"delete from department where parent_id = ?",pid);
    }

    @Override
    public Department queryById(int id) throws SQLException {
        return Db.runner0().query("select * from department where id =?"
                ,new BeanHandler<>(Department.class),id);
    }

    @Override
    public List<Department> queryPidZero() throws SQLException {
        return Db.runner0().query("select * from department where parent_id = 0",new BeanListHandler<>(Department.class));
    }

    @Override
    public List<Department> queryPidNotZero() throws SQLException {
        return Db.runner0().query("select * from department where parent_id > 0",new BeanListHandler<>(Department.class));
    }

    @Override
    public List<Department> queryAll() throws SQLException {
        return Db.runner0().query("select * from department",new BeanListHandler<>(Department.class));
    }

    @Override
    public Department getById(int depid) throws SQLException {
        return Db.runner0().query("select * from department where id=?",new BeanHandler<>(Department.class),
                depid);
    }

    @Override
    public List<Department> queryPidDZero() throws SQLException {
        return Db.runner0().query("select * from department where parent_id >0 ",new BeanListHandler<>(Department.class));
    }

    @Override
    public void addNum(Department department) throws SQLException {
        Db.runner().update(Db.getConnection(),"update department set staffs =? where id=?",
                department.getStaffs(),department.getId());
    }

    @Override
    public Department getByDid(int id) throws SQLException {
        return Db.runner0().query("select * from department where did=?",
                new BeanHandler<>(Department.class),id);
    }

    @Override
    public List<Department> findByPid(int pid) throws SQLException {
        return Db.runner0().query("select * from department where parent_id=?",
                new BeanListHandler<>(Department.class),pid);
    }

    @Override
    public void setbed(int depid) throws SQLException {
        Db.runner().update(Db.getConnection(),"update department set beds=beds+1 where id=?",
                depid);
    }

    @Override
    public void setbed1(int depid) throws SQLException {
        Db.runner().update(Db.getConnection(),"update department set beds=beds-1 where id=?",
                depid);
    }

    @Override
    public List<StaffsCount> getStaffsData() throws SQLException {
        return Db.runner0().query("SELECT n.name,n.ncount,d.dcount from\n" +
                "(select dep.name,count(nur.id) as ncount\n" +
                "from  nurse nur right join department dep\n" +
                "on nur.depid = dep.id\n" +
                "where dep.parent_id > 0\n" +
                "GROUP BY dep.id ) n\n" +
                "INNER JOIN\n" +
                "(select dep.name,count(*) as dcount\n" +
                "from department dep left join doctor doc\n" +
                "on dep.id = doc.depid \n" +
                "where dep.parent_id > 0\n" +
                "GROUP BY dep.id) d \n" +
                "on n.name=d.name",new BeanListHandler<>(StaffsCount.class));
    }

}
